﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Enums;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;

namespace PMAF.K3Cloud.SCM.Report.ServicePlugIn
{
    [HotUpdate]
    [Description("客户保证金汇总表")]
    public class CustEarnestSumReport : SysReportBaseService
    {
        public override void Initialize()
        {
            //初始化
            base.Initialize();
            //简单账表类型：普通、树形、分页
            ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            //报表显示顺序字段
            //ReportProperty.IdentityFieldName = "FIDENTITYID";
            //是用插件创建临时表
            IsCreateTempTableByPlugin = true;
            //是否分组汇总
            ReportProperty.IsGroupSummary = true;
        }

        /// <summary>
        /// 设置报表列
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            header.AddChild("ORGNAME", new LocaleValue("结算组织", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("CUSTNUMBER", new LocaleValue("客户编码", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("CUSTNAME", new LocaleValue("客户名称", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("CURRENCYNAME", new LocaleValue("币别", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("BEGINBALANCE", new LocaleValue("期初余额", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FREALRECAMOUNTFOR", new LocaleValue("本期收保证金", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FRECTOTALAMOUNTFOR", new LocaleValue("本期保证金转货款", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FREALREFUNDAMOUNTFOR", new LocaleValue("本期退保证金", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("ENDBALANCE", new LocaleValue("期末余额", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            return header;
        }

        /// <summary>
        /// SQL语句构建报表数据
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="tableName"></param>
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            string filterOrg = string.IsNullOrWhiteSpace(customFilter["F_PMAF_SettleOrg"].ToString())
                ? " " : string.Format(" AND FSETTLEORGID IN ({0}) ", Convert.ToString(customFilter["F_PMAF_SettleOrg"]));
            string startDate = (customFilter["F_PMAF_BeginDate"] == null)
                ? string.Empty : Convert.ToDateTime(customFilter["F_PMAF_BeginDate"]).ToString("yyyy-MM-dd");
            string endDate = (customFilter["F_PMAF_EndDate"] == null)
                ? string.Empty : Convert.ToDateTime(customFilter["F_PMAF_EndDate"]).ToString("yyyy-MM-dd");
            DynamicObject startCustomer = customFilter["F_PMAF_BeginCustomer"] as DynamicObject;
            string startCustomerStr = (startCustomer == null)
                ? string.Empty : string.Format(" AND FCONTACTUNIT >= {0} ", Convert.ToString(startCustomer["Id"]));
            DynamicObject endCustomer = customFilter["F_PMAF_EndCustomer"] as DynamicObject;
            string endCustomerStr = (endCustomer == null)
                ? string.Empty : string.Format(" AND FCONTACTUNIT <= {0} ", Convert.ToString(endCustomer["Id"]));
            string sqlStr = string.Format(@"/*dialect*/
                SELECT t0.FSETTLEORGID,t0.FCONTACTUNIT,t0.FCURRENCYID,SUM(t0.FREALRECAMOUNTFOR) AS FREALRECAMOUNTFOR
                INTO #RECEIVEBEGINPERIOD
                FROM T_AR_RECEIVEBILL t0
                WHERE t0.FBILLTYPEID='58c77e15e6566a'
                AND t0.FDATE < '{0}'{2}{3}{4}
                GROUP BY t0.FSETTLEORGID,t0.FCONTACTUNIT,t0.FCURRENCYID;
                SELECT t0.FSETTLEORGID,t0.FCONTACTUNIT,t0.FCURRENCYID,SUM(t0.FREALREFUNDAMOUNTFOR) AS FREALREFUNDAMOUNTFOR
                INTO #REFUNDBEGINPERIOD
                FROM T_AR_REFUNDBILL t0
                WHERE t0.FBILLTYPEID='58c7aa52e6592e'
                AND t0.FDATE < '{0}'{2}{3}{4}
                GROUP BY t0.FSETTLEORGID,t0.FCONTACTUNIT,t0.FCURRENCYID;
                SELECT a0.FSETTLEORGID,a0.FCONTACTUNIT,a0.FCURRENCYID,a0.FREALRECAMOUNTFOR,ISNULL(a1.FREALREFUNDAMOUNTFOR,0) AS FREALREFUNDAMOUNTFOR
                INTO #BEGINPERIOD
                FROM #RECEIVEBEGINPERIOD a0
                LEFT JOIN #REFUNDBEGINPERIOD a1 ON a0.FCONTACTUNIT=a1.FCONTACTUNIT AND a0.FSETTLEORGID=a1.FSETTLEORGID AND a0.FCURRENCYID=a1.FCURRENCYID;
                SELECT t0.FSETTLEORGID,t0.FCONTACTUNIT,t0.FCURRENCYID,SUM(t0.FREALRECAMOUNTFOR) AS FREALRECAMOUNTFOR
                INTO #RECEIVEPERIOD
                FROM T_AR_RECEIVEBILL t0
                WHERE t0.FBILLTYPEID='58c77e15e6566a'
                AND (t0.FDATE >= '{0}' AND t0.FDATE < '{1}'){2}{3}{4}
                GROUP BY t0.FSETTLEORGID,t0.FCONTACTUNIT,t0.FCURRENCYID;
                SELECT t0.FSETTLEORGID,t0.FCONTACTUNIT,t0.FCURRENCYID,SUM(t0.FREALREFUNDAMOUNTFOR) AS FREALREFUNDAMOUNTFOR
                INTO #REFUNDPERIOD
                FROM T_AR_REFUNDBILL t0
                WHERE t0.FBILLTYPEID='58c7aa52e6592e'
                AND (t0.FDATE >= '{0}' AND t0.FDATE <= '{1}'){2}{3}{4}
                GROUP BY t0.FSETTLEORGID,t0.FCONTACTUNIT,t0.FCURRENCYID;
                SELECT t1.FSETTLEORGID,t1.FCONTACTUNIT,t1.FCURRENCYID,SUM(t0.FRECTOTALAMOUNTFOR) AS FRECTOTALAMOUNTFOR
                INTO #CONVERTPERIOD
                FROM T_AR_RECEIVEBILLENTRY t0
                LEFT JOIN T_AR_RECEIVEBILL t1 ON t0.FID=t1.FID
                WHERE t0.FSETTLETYPEID=30700
                AND t1.FBILLTYPEID='36cf265bd8c3452194ed9c83ec5e73d2'
                AND (t1.FDATE >= '{0}' AND t1.FDATE <= '{1}'){2}{3}{4}
                GROUP BY t1.FSETTLEORGID,t1.FCONTACTUNIT,t1.FCURRENCYID;
                SELECT
                b0.FSETTLEORGID AS B0FSETTLEORGID,
                b0.FCONTACTUNIT AS B0FCONTACTUNIT,
                b0.FCURRENCYID AS B0FCURRENCYID,
                ISNULL(b0.FREALRECAMOUNTFOR-b0.FREALREFUNDAMOUNTFOR,0) AS BEGINBALANCE,
                b1.FSETTLEORGID AS B1FSETTLEORGID,
                b1.FCONTACTUNIT AS B1FCONTACTUNIT,
                b1.FCURRENCYID AS B1FCURRENCYID,
                ISNULL(b1.FREALRECAMOUNTFOR,0) AS FREALRECAMOUNTFOR,
                b2.FSETTLEORGID AS B2FSETTLEORGID,
                b2.FCONTACTUNIT AS B2FCONTACTUNIT,
                b2.FCURRENCYID AS B2FCURRENCYID,
                ISNULL(b2.FREALREFUNDAMOUNTFOR,0) AS FREALREFUNDAMOUNTFOR,
                b3.FSETTLEORGID AS B3FSETTLEORGID,
                b3.FCONTACTUNIT AS B3FCONTACTUNIT,
                b3.FCURRENCYID AS B3FCURRENCYID,
                ISNULL(b3.FRECTOTALAMOUNTFOR,0) AS FRECTOTALAMOUNTFOR
                INTO #CUSTEARNESTREPORT
                FROM #BEGINPERIOD b0
                FULL JOIN #RECEIVEPERIOD b1 ON b0.FCONTACTUNIT=b1.FCONTACTUNIT AND b0.FSETTLEORGID=b1.FSETTLEORGID AND b0.FCURRENCYID=b1.FCURRENCYID
                FULL JOIN #REFUNDPERIOD b2 ON (b1.FCONTACTUNIT=b2.FCONTACTUNIT AND b1.FSETTLEORGID=b2.FSETTLEORGID AND b1.FCURRENCYID=b2.FCURRENCYID)
                OR (b0.FCONTACTUNIT=b2.FCONTACTUNIT AND b0.FSETTLEORGID=b2.FSETTLEORGID AND b0.FCURRENCYID=b2.FCURRENCYID)
                FULL JOIN #CONVERTPERIOD b3 ON (b0.FCONTACTUNIT=b3.FCONTACTUNIT AND b0.FSETTLEORGID=b3.FSETTLEORGID AND b0.FCURRENCYID=b3.FCURRENCYID)
                OR (b1.FCONTACTUNIT=b3.FCONTACTUNIT AND b1.FSETTLEORGID=b3.FSETTLEORGID AND b1.FCURRENCYID=b3.FCURRENCYID)
                OR (b2.FCONTACTUNIT=b3.FCONTACTUNIT AND b2.FSETTLEORGID=b3.FSETTLEORGID AND b2.FCURRENCYID=b3.FCURRENCYID);
                UPDATE #CUSTEARNESTREPORT SET B0FSETTLEORGID=B1FSETTLEORGID WHERE B0FSETTLEORGID IS NULL AND B1FSETTLEORGID IS NOT NULL;
                UPDATE #CUSTEARNESTREPORT SET B0FCONTACTUNIT=B1FCONTACTUNIT WHERE B0FCONTACTUNIT IS NULL AND B1FCONTACTUNIT IS NOT NULL;
                UPDATE #CUSTEARNESTREPORT SET B0FCURRENCYID=B1FCURRENCYID WHERE B0FCURRENCYID IS NULL AND B1FCURRENCYID IS NOT NULL;
                UPDATE #CUSTEARNESTREPORT SET B0FSETTLEORGID=B2FSETTLEORGID WHERE B0FSETTLEORGID IS NULL AND B2FSETTLEORGID IS NOT NULL;
                UPDATE #CUSTEARNESTREPORT SET B0FCONTACTUNIT=B2FCONTACTUNIT WHERE B0FCONTACTUNIT IS NULL AND B2FCONTACTUNIT IS NOT NULL;
                UPDATE #CUSTEARNESTREPORT SET B0FCURRENCYID=B2FCURRENCYID WHERE B0FCURRENCYID IS NULL AND B2FCURRENCYID IS NOT NULL;
                UPDATE #CUSTEARNESTREPORT SET B0FSETTLEORGID=B3FSETTLEORGID WHERE B0FSETTLEORGID IS NULL AND B3FSETTLEORGID IS NOT NULL;
                UPDATE #CUSTEARNESTREPORT SET B0FCONTACTUNIT=B3FCONTACTUNIT WHERE B0FCONTACTUNIT IS NULL AND B3FCONTACTUNIT IS NOT NULL;
                UPDATE #CUSTEARNESTREPORT SET B0FCURRENCYID=B3FCURRENCYID WHERE B0FCURRENCYID IS NULL AND B3FCURRENCYID IS NOT NULL;
                SELECT
                ROW_NUMBER() OVER(ORDER BY c2.FNUMBER) AS FIDENTITYID,
                c1.FNAME AS ORGNAME,
                c2.FNUMBER AS CUSTNUMBER,
                c3.FNAME AS CUSTNAME,
                c4.FNAME AS CURRENCYNAME,
                c0.BEGINBALANCE,
                c0.FREALRECAMOUNTFOR,
                c0.FRECTOTALAMOUNTFOR,
                c0.FREALREFUNDAMOUNTFOR,
                (c0.BEGINBALANCE+c0.FREALRECAMOUNTFOR-c0.FRECTOTALAMOUNTFOR-c0.FREALREFUNDAMOUNTFOR) AS ENDBALANCE
                INTO {5}
                FROM #CUSTEARNESTREPORT c0
                LEFT JOIN T_ORG_ORGANIZATIONS_L c1 ON c0.B0FSETTLEORGID=c1.FORGID AND c1.FLOCALEID=2052
                LEFT JOIN T_BD_CUSTOMER c2 ON c0.B0FCONTACTUNIT=c2.FCUSTID
                LEFT JOIN T_BD_CUSTOMER_L c3 ON c0.B0FCONTACTUNIT=c3.FCUSTID AND c3.FLOCALEID=2052
                LEFT JOIN T_BD_CURRENCY_L c4 ON c0.B0FCURRENCYID=c4.FCURRENCYID AND c4.FLOCALEID=2052;
                DROP TABLE #RECEIVEBEGINPERIOD;
                DROP TABLE #REFUNDBEGINPERIOD;
                DROP TABLE #BEGINPERIOD;
                DROP TABLE #RECEIVEPERIOD;
                DROP TABLE #REFUNDPERIOD;
                DROP TABLE #CONVERTPERIOD;
                DROP TABLE #CUSTEARNESTREPORT;
            ", startDate, endDate, filterOrg, startCustomerStr, endCustomerStr, tableName);
            DBUtils.ExecuteDynamicObject(Context, sqlStr);
        }



        /// <summary>
        /// 设置汇总列信息
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            var result = base.GetSummaryColumnInfo(filter);
            result.Add(new SummaryField("BEGINBALANCE", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("FREALRECAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("FRECTOTALAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("FREALREFUNDAMOUNTFOR", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("ENDBALANCE", BOSEnums.Enu_SummaryType.SUM));
            return result;
        }
    }
}
